﻿namespace OneWorks.Utils.AdoNet
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using Log;

    /// <summary>   
    /// 通用数据库访问类，封装了对数据库的常见操作   
    /// </summary>   
    public sealed class DbUtility : IDbUtility
    {

        private readonly System.Data.Common.DbProviderFactory _providerFactory;

        private readonly string _connectionString;

        private readonly DbProviderType _dbType;

        /// <summary>   
        /// 构造函数   
        /// </summary>   
        /// <param name="connectionString">数据库连接字符串</param>   
        /// <param name="providerType">数据库类型枚举</param>   
        public DbUtility(string connectionString, DbProviderType providerType)
        {
            _connectionString = connectionString;
            _dbType = providerType;
            _providerFactory = ProviderFactory.GetDbProviderFactory(providerType);
            if (_providerFactory == null)
            {
                LogHandler.Log.Error(string.Format("创建数据库对象出错,connectionString:{0},providerType:{1}", connectionString, providerType));
            }
        }


        #region 增删改操作
        /// <summary>      
        /// 对数据库执行增删改操作，返回受影响的行数。      
        /// </summary>      
        /// <param name="sql">要执行的增删改的SQL语句</param>      
        /// <returns></returns>     
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null, CommandType.Text);
        }

        /// <summary>      
        /// 对数据库执行增删改操作，返回受影响的行数。      
        /// </summary>      
        /// <param name="sql">要执行的增删改的SQL语句</param>      
        /// <param name="parameters">执行增删改语句所需要的参数</param>   
        /// <returns></returns>     
        public int ExecuteNonQuery(string sql, IList<DbParameter> parameters)
        {
            return ExecuteNonQuery(sql, parameters, CommandType.StoredProcedure);
        }

        /// <summary>      
        /// 对数据库执行增删改操作，返回受影响的行数。      
        /// </summary>      
        /// <param name="sql">要执行的增删改的SQL语句</param>      
        /// <param name="parameters">执行增删改语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>   
        public int ExecuteNonQuery(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (var command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                var affectedRows = command.ExecuteNonQuery();
                command.Connection.Close();
                return affectedRows;
            }
        }
        #endregion


        /// <summary>      
        /// 执行一个查询语句，返回一个关联的DataReader实例      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <returns></returns>    
        public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters)
        {
            return ExecuteReader(sql, parameters, CommandType.Text);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个关联的DataReader实例      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>    
        public DbDataReader ExecuteReader(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            var command = CreateDbCommand(sql, parameters, commandType);
            command.Connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <returns></returns>   
        public DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null, CommandType.Text);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <returns></returns>   
        public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters)
        {
            return ExecuteDataTable(sql, parameters, CommandType.StoredProcedure);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>   
        public DataTable ExecuteDataTable(string sql, IList<DbParameter> parameters, CommandType commandType)
        {

            using (var command = CreateDbCommand(sql, parameters, commandType))
            {
                using (var adapter = _providerFactory.CreateDataAdapter())
                {

                    if (adapter != null) adapter.SelectCommand = command;
                    var data = new DataTable();
                    if (adapter != null) adapter.Fill(data);
                    return data;
                }
            }
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <returns></returns>   
        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null, CommandType.Text);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <returns></returns>   
        public DataSet ExecuteDataSet(string sql, IList<DbParameter> parameters)
        {
            return ExecuteDataSet(sql, parameters, CommandType.StoredProcedure);
        }

        /// <summary>      
        /// 执行一个查询语句，返回一个包含查询结果的DataTable      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>   
        public DataSet ExecuteDataSet(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (var command = CreateDbCommand(sql, parameters, commandType))
            {
                using (var adapter = _providerFactory.CreateDataAdapter())
                {

                    if (adapter != null) adapter.SelectCommand = command;
                    var data = new DataSet();
                    if (adapter != null) adapter.Fill(data);
                    return data;

                }
            }
        }

        /// <summary>      
        /// 执行一个查询语句，返回查询结果的第一行第一列      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>      
        /// <returns></returns>      
        public Object ExecuteScalar(string sql, IList<DbParameter> parameters)
        {
            return ExecuteScalar(sql, parameters, CommandType.Text);
        }

        /// <summary>      
        /// 执行一个查询语句，返回查询结果的第一行第一列      
        /// </summary>      
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>      
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>      
        public Object ExecuteScalar(string sql, IList<DbParameter> parameters, CommandType commandType)
        {
            using (var command = CreateDbCommand(sql, parameters, commandType))
            {
                command.Connection.Open();
                var result = command.ExecuteScalar();
                command.Connection.Close();
                return result;
            }
        }

        #region 参数
        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter CreateDbParameter(string name, object value)
        {
            return CreateDbParameter(name, ParameterDirection.Input, value);
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="parameterDirection"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbParameter CreateDbParameter(string name, ParameterDirection parameterDirection, object value)
        {
            var parameter = _providerFactory.CreateParameter();
            if (parameter == null)
            {
                LogHandler.Log.Error(string.Format("创建数据库DbParameter对象出错,name:{0},object:{1},ParameterDirection:{2}", name, value, parameterDirection));
                return null;
            }
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = parameterDirection;
            return parameter;

        }
        #endregion

        /// <summary>   
        /// 创建一个DbCommand对象   
        /// </summary>   
        /// <param name="sql">要执行的查询语句</param>      
        /// <param name="parameters">执行SQL查询语句所需要的参数</param>   
        /// <param name="commandType">执行的SQL语句的类型</param>   
        /// <returns></returns>   
        private DbCommand CreateDbCommand(string sql, ICollection<DbParameter> parameters, CommandType commandType)
        {
            var connection = _providerFactory.CreateConnection();
            var command = _providerFactory.CreateCommand();
            if (connection != null) connection.ConnectionString = _connectionString;
            if (command != null)
            {
                command.CommandText = sql;
                command.CommandType = commandType;
                command.Connection = connection;
            }
            if (!(parameters == null || parameters.Count == 0) && command != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /// <summary>
        /// 获取数据连接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConnectionString()
        {
            return _connectionString;
        }


        public DbProviderType GetDbType()
        {
            return _dbType;
        }
    }
}
